package uy.edu.um.ltic.server.persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import uy.edu.um.ltic.exception.DataBaseException;
import uy.edu.um.ltic.server.pool.DataBaseConnectionMgr;

public class ConferenceRemStarDAOMgr implements ConferenceRemStarDAOMgt{

	private static ConferenceRemStarDAOMgr instance = null;

	private ConferenceRemStarDAOMgr() {
	}

	public static ConferenceRemStarDAOMgr getInstance() {
		if (instance == null) {
			instance = new ConferenceRemStarDAOMgr();
		}
		return instance;
	}
	@Override
	public void createAll() throws DataBaseException {
		Connection oConnection = this.openConnection();
		createPendReminders(oConnection);
		createPendStartConf(oConnection);
		try {
			oConnection.close();
		} catch (SQLException e) {
			throw new DataBaseException();
		}
	}

	private void createPendStartConf(Connection oConnection) throws DataBaseException {
		String createTableT = "CREATE TABLE PendingStartConf (sUser INT, "
				+ "iDConf INT, "
				+ "PRIMARY KEY (sUser, iDConf), "
				+ "FOREIGN KEY (sUser) REFERENCES Employees(nEmployee) ON DELETE CASCADE ON UPDATE CASCADE, "
				+ "FOREIGN KEY (iDConf) REFERENCES Conference(idConference) ON DELETE CASCADE ON UPDATE CASCADE);";
		Statement oStatementT;
		try {
			oStatementT = oConnection.createStatement();
			oStatementT.execute(createTableT);
		} catch (SQLException e) {
			throw new DataBaseException();
		}
	}

	private void createPendReminders(Connection oConnection) throws DataBaseException {
		String createTableT = "CREATE TABLE PendingReminders (sUser INT, "
				+ "iDConf INT, "
				+ "PRIMARY KEY (sUser, iDConf), "
				+ "FOREIGN KEY (sUser) REFERENCES Employees(nEmployee) ON DELETE CASCADE ON UPDATE CASCADE, "
				+ "FOREIGN KEY (iDConf) REFERENCES Conference(idConference) ON DELETE CASCADE ON UPDATE CASCADE);";
		Statement oStatementT;
		try {
			oStatementT = oConnection.createStatement();
			oStatementT.execute(createTableT);
		} catch (SQLException e) {
			throw new DataBaseException();
		}
	}

	/*@Override
	public void addReminder(int receiverDisconnected, int iDConf) throws DataBaseException {
		Connection oConnection = this.openConnection();
		String insert = "INSERT INTO PendingReminders (sUser, iDConf) VALUES (?, ?)";
		PreparedStatement oPreparedStatement;
		try {
			oPreparedStatement = oConnection.prepareStatement(insert);

			oPreparedStatement.setInt(1, receiverDisconnected);
			oPreparedStatement.setInt(2, iDConf);

			oPreparedStatement.execute();
			oPreparedStatement.close();
		} catch (SQLException e) {
			throw new DataBaseException();
		} finally {
			try {
				oConnection.close();
			} catch (SQLException e) {
				throw new DataBaseException();
			}
		}
	}*/
/*
	@Override
	public ArrayList<Integer> getReminder(int receiverDisconnected) throws DataBaseException {
		Connection oConnection = this.openConnection();
		ArrayList<Integer> result = new ArrayList<Integer>();
		String get = "SELECT iDConf FROM PendingReminders WHERE sUser = ?";
		PreparedStatement oPreparedStatement;
		try {
			oPreparedStatement = oConnection.prepareStatement(get);

			oPreparedStatement.setInt(1, receiverDisconnected);
			
			ResultSet oResult = oPreparedStatement.executeQuery();
			while (oResult.next()){
				result.add(oResult.getInt(1));
			}
			oResult.close();
			oPreparedStatement.close();
		} catch (SQLException e) {
			throw new DataBaseException();
		} finally {
			try {
				oConnection.close();
			} catch (SQLException e) {
				throw new DataBaseException();
			}
		}
		return result;
	}
*/
	/*@Override
	public void deleteReminder(int receiverDisconnected) throws DataBaseException {
		Connection oConnection = this.openConnection();
		String delete = "DELETE FROM PendingTextMessages WHERE sUser = ?";
		PreparedStatement oPreparedStatement;
		try {
			oPreparedStatement = oConnection.prepareStatement(delete);

			oPreparedStatement.setInt(1, receiverDisconnected);

			oPreparedStatement.execute();
			oPreparedStatement.close();
		} catch (SQLException e) {
			throw new DataBaseException();
		} finally {
			try {
				oConnection.close();
			} catch (SQLException e) {
				throw new DataBaseException();
			}
		}
	}
*/
	@Override
	public void addStartConf(int receiverDisconnected, int idConference) throws DataBaseException {
		boolean yaesta = false;
		if (ConferenceRemStarDAOFactory.getInstance().getConferenceRemStarDAOMgt().getStartConf(receiverDisconnected).size()!=0){
			for(Integer nroconf : ConferenceRemStarDAOFactory.getInstance().getConferenceRemStarDAOMgt().getStartConf(receiverDisconnected)){
				if (nroconf.compareTo(idConference) == 0){
					yaesta = true;
				}
			}
		}
		if(!yaesta){
		Connection oConnection = this.openConnection();
		String insert = "INSERT INTO PendingStartConf (sUser, iDConf) VALUES (?, ?)";
		PreparedStatement oPreparedStatement;
		try {
			oPreparedStatement = oConnection.prepareStatement(insert);

			oPreparedStatement.setInt(1, receiverDisconnected);
			oPreparedStatement.setInt(2, idConference);

			oPreparedStatement.execute();
			oPreparedStatement.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataBaseException();
			
		} finally {
			try {
				oConnection.close();
			} catch (SQLException e) {
				throw new DataBaseException();
			}
		}
		}
	}

	@Override
	public ArrayList<Integer> getStartConf(int receiverDisconnected) throws DataBaseException {
		Connection oConnection = this.openConnection();
		ArrayList<Integer> result = new ArrayList<Integer>();
		String get = "SELECT iDConf FROM PendingStartConf WHERE sUser = ?";
		PreparedStatement oPreparedStatement;
		try {
			oPreparedStatement = oConnection.prepareStatement(get);

			oPreparedStatement.setInt(1, receiverDisconnected);
			
			ResultSet oResult = oPreparedStatement.executeQuery();
			while (oResult.next()){
				result.add(oResult.getInt(1));
			}
			oResult.close();
			oPreparedStatement.close();
		} catch (SQLException e) {
			throw new DataBaseException();
		} finally {
			try {
				oConnection.close();
			} catch (SQLException e) {
				throw new DataBaseException();
			}
		}
		return result;
	}

	@Override
	public void deleteStartConf(int receiverDisconnected) throws DataBaseException {
		Connection oConnection = this.openConnection();
		String delete = "DELETE FROM PendingStartConf WHERE sUser = ?";
		PreparedStatement oPreparedStatement;
		try {
			oPreparedStatement = oConnection.prepareStatement(delete);

			oPreparedStatement.setInt(1, receiverDisconnected);

			oPreparedStatement.execute();
			oPreparedStatement.close();
		} catch (SQLException e) {
			throw new DataBaseException();
		} finally {
			try {
				oConnection.close();
			} catch (SQLException e) {
				throw new DataBaseException();
			}
		}
	}
	
	@Override
	public Connection openConnection() throws DataBaseException {
		Connection oConnection = null;
		try {
			oConnection = DataBaseConnectionMgr.getInstance().getConnection();
		} catch (SQLException e2) {
			 throw new DataBaseException();
		}
		return oConnection;
	}
	
}
